package com.study.manage.dal;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.study.manage.domain.BlogStatDay;
import com.study.manage.domain.enumm.BlogStatDayType;

public interface BlogStatDayRepository extends PagingAndSortingRepository<BlogStatDay, Long> {

    @Query("select sum(t.count) from BlogStatDay t where t.evaluateId in (:evaluateIds) and t.type=:type"
            + " and (to_days(t.addAt)=to_days(:date) or :date is null)")
    Integer sumCountByEvaluateIdInAndTypeAndDate(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("type") BlogStatDayType type, @Param("date") Date date);

    @Query("select sum(t.count) from BlogStatDay t where t.blogId in (:blogId) and t.type=:type"
            + " and (to_days(t.addAt)=to_days(:date) or :date is null)")
    Integer sumCountByBlogIdAndTypeAndDate(@Param("blogId") Long blogId,
            @Param("type") BlogStatDayType type, @Param("date") Date date);


    @Query("select sum(t.count) from BlogStatDay t where t.evaluateId in (:evaluateIds) and t.type in (:type)"
            + " and (to_days(t.addAt)=to_days(:date) or :date is null)")
    Integer sumCountByEvaluateIdInAndTypeInAndDate(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("type") List<BlogStatDayType> type, @Param("date") Date date);


    @Query("select t.createdAt,sum(t.count),'帖子浏览数' as type from BlogStatDay t  where t.type=0 and t.evaluateId in (:evaluateIds) "
            + "and to_days(t.createdAt)>=to_days(:beginAt) and to_days(t.createdAt)<=to_days(:endAt) group by to_days(t.createdAt)")
    List<Object[]> countShowByEvaluateIdInGroupByDay(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

    @Query("select t.createdAt,sum(t.count),'帖子打开数' as type from BlogStatDay t  where t.type=1 and t.evaluateId in (:evaluateIds) "
            + "and to_days(t.createdAt)>=to_days(:beginAt) and to_days(t.createdAt)<=to_days(:endAt) group by to_days(t.createdAt)")
    List<Object[]> countOpendByEvaluateIdInGroupByDay(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);


    @Query(nativeQuery = true,
            value = "select t.created_at,sum(t.count),'互动总数' as type from sa_blog_stat_day t  where t.typee in (2,3,4,5,6) and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.count),'点赞数量' as type  from sa_blog_stat_day t  where t.typee=2 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.count),'打分数量' as type  from sa_blog_stat_day t  where t.typee=3 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.count),'点评数量' as type  from sa_blog_stat_day t  where t.typee=4 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.count),'讨论数量' as type  from sa_blog_stat_day t  where t.typee=5 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.count),'投票数量' as type  from sa_blog_stat_day t  where t.typee=6 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)")
    List<Object[]> countHudongByEvaluateIdInGroupByDays(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

}
